MySQL provides two important functions for working with date strings: STR_TO_DATE() for converting strings into date values, and DATE_FORMAT() for converting date values into formatted strings.

1. STR_TO_DATE(): Convert String → Date

1
Parses a string based on a specified format and returns a DATE, DATETIME, or TIME value.
2
Useful when importing data where dates are stored as strings.
3
Format specifiers (e.g., %d, %m, %Y) must match the input string.

STR_TO_DATE() Example

This converts the string '21-11-2025' into a proper DATE value: 2025-11-21.

2. DATE_FORMAT(): Convert Date → String

1
Formats a date value into a custom string.
2
Useful for displaying dates in user-friendly formats.
3
Requires format specifiers similar to STR_TO_DATE().

DATE_FORMAT() Example

This outputs: '21/11/2025'.

3. Key Differences Between STR_TO_DATE() and DATE_FORMAT()

1
STR_TO_DATE() converts a string into a date value.
2
DATE_FORMAT() converts a date into a formatted string.
3
STR_TO_DATE() is used when reading/parsing dates; DATE_FORMAT() is used when displaying dates.
4
Both rely on the same set of format specifiers.

In summary: Use STR_TO_DATE() to interpret a date string into a real MySQL date, and DATE_FORMAT() to output a stored date in a human-readable format.

Difficulty: 5/10

Topics: date parsing, date formatting, SQL functions

Scenario Questions

0-2 years experience

1
We have a CSV import where dates appear as '31/12/2022'. Write the INSERT statement that converts that string into a DATE column using MySQL functions.
2
If you run SELECT DATE_FORMAT('2022-07-15', '%b %d, %Y'), what string is returned and why?

2-5 years experience

1
Our reporting feature expects dates in 'YYYYMMDD' format, but the source table stores them as VARCHAR in 'MM-DD-YYYY'. How would you write a SELECT that returns the dates correctly converted and formatted?
2
During a data migration some rows contain malformed date strings, causing STR_TO_DATE to return NULL. How would you identify those rows and provide a fallback value in a single query?

5-8 years experience

1
We need a daily summary table that aggregates events by date, yet event timestamps are stored as strings in several legacy formats across tables. Describe a robust conversion pipeline using STR_TO_DATE and DATE_FORMAT, addressing performance and maintainability.
2
Our application serves millions of users and frequently formats dates for UI. Discuss the trade‑offs of doing date formatting in MySQL with DATE_FORMAT versus handling it in the application layer.

8+ years experience

1
Our organization is migrating from MySQL to a polyglot data platform. How would you refactor all existing STR_TO_DATE and DATE_FORMAT usage to ensure consistency, backward compatibility, and minimal disruption across services?
2
Multiple microservices store dates in different string representations due to historic reasons. Propose an architectural strategy to standardize date handling across the ecosystem, including migration plan, testing, and governance.

Follow-up Questions

❓What edge cases might cause STR_TO_DATE to return NULL?
❓How would you test that your formatting works for all locales?
❓Can you estimate the performance impact of formatting millions of rows in a query?